const sql = require('mssql');
const baseFn = require('../../public/base.js');
/**
 * 
 * 货位架管理
 */

/**
 * 
 * 获取商品信息
 */
const getCarriageRackShopInfo = async (ctx, next) => {
    const { index, pageSize, searchVal = '' } = ctx.request.body;
    let publicSql = ` FROM (SELECT CASE WHEN CCTJ IN ('阴凉' , '冷藏' ,'冷暗') THEN 'L' WHEN CCTJ = '常温' THEN 'W' ELSE '' END AS GCCTJ,
    * FROM BM_YP) B LEFT JOIN YPZL Y ON B.BM = Y.YPBM  LEFT JOIN YP_HWBH H ON B.BM = H.YPBM LEFT JOIN (SELECT SUM(KCSL) AS KCSL,BMBM,YPBM FROM KC_KCDTB GROUP BY BMBM,
    YPBM) KC ON H.BMBM = KC.BMBM AND B.BM = KC.YPBM   WHERE QY_FLAG = 'T' `;
    let selectSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY H.HWBH DESC) AS px,B.BM,B.MC,B.GG,B.SCCJ,B.CDMC,B.PZWH,Y.JXMC,Y.NFWY,Y.CCTJ,
    Y.SPLX,H.BMBM,H.CKBM,H.HWBH,KC.KCSL,B.CCTJ AS ZLCCTJ  ${publicSql} `;

    let totalSql = `SELECT COUNT(B.BM) AS total  ${publicSql} `;

    let searchListSql = [];
    if (searchVal) {
        if (searchVal.searchValue) {
            searchListSql.push(` AND (B.MC LIKE '%${searchVal.searchValue}%' OR B.BM LIKE '%${searchVal.searchValue}%' OR B.JP LIKE '%${searchVal.searchValue}%' OR B.TM LIKE '%${searchVal.searchValue}%' ) `);
        }
        if (searchVal.CCTJ) {
            searchListSql.push(` AND B.CCTJ = '${searchVal.CCTJ}' `);
        }
        if (searchVal.ISCCTJ) {
            if (searchVal.ISCCTJ === 'F') {
                searchListSql.push(` AND  B.GCCTJ != Y.CCTJ `);
            } else {
                searchListSql.push(` AND  B.GCCTJ = Y.CCTJ `);
            }

        }
        if (searchVal.HWBH) {
            searchListSql.push(` AND H.HWBH LIKE '%${searchVal.HWBH}%' `);
        }
        if (searchVal.JXMC) {
            searchListSql.push(` AND Y.JXMC = '${searchVal.JXMC}' `);
        }
        if (searchVal.useTime) {
            let nowTime = baseFn.getNowTimeId().fullDate;
            let agoTime = baseFn.getDayAgo(searchVal.useTime);
            searchListSql.push(
                ` AND KC.KCSL <= 0 AND H.HWBH IS NOT NULL AND B.BM NOT IN (SELECT XM.YPBM FROM XTD_MX XM LEFT JOIN XTD_ZB XZ ON XM.ID = XZ.ID WHERE XZ.RQ>='${agoTime}' AND XZ.RQ<='${nowTime}') 
                AND  B.BM NOT IN (SELECT RM.YPBM FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID WHERE RZ.RQ>='${agoTime}' AND RZ.RQ<='${nowTime}') `);
        }
    }

    totalSql += searchListSql.join(' ');
    selectSql += ` ${searchListSql.join(' ')} ) AS t1 WHERE t1.px between ${(index - 1) * pageSize + 1} and ${index * pageSize}`;
    let total = await sql.query(totalSql);
    let result = await sql.query(selectSql);
    ctx.body = { total: total.recordset[0].total, result: result.recordset }
}
// 把剂型的中文转成英文
const judgeJx = (jx) => {
    let result = '';
    let jxList = {
        P: '片剂',
        J: '胶囊',
        W: '丸剂',
        Y: '液体制剂',
        G: '固体制剂'
    }
    for (let item in jxList) {
        if (jx.replace(/\s+/g, "") == jxList[item]) {
            result = item;
        }
    }
    return result;
}
// 获取能匹配的货架位
const getCanUseHwbh = async (ctx, next) => {
    const body = ctx.request.body;
    let searchShop = await sql.query(`SELECT * FROM YPZL L WHERE L.YPBM = '${body.YPBM}'`);
    let hwlx = '';
    let hwbhRangeSql = `SELECT H.HWBH FROM HWBH H WHERE H.HWBH NOT IN (SELECT Y.HWBH FROM YP_HWBH Y WHERE Y.BMBM = '${body.BMBM}') `;

    if (searchShop.recordset[0].SPLX === 'Y') {
        hwlx = searchShop.recordset[0].SPLX + searchShop.recordset[0].NFWY + judgeJx(searchShop.recordset[0].JXMC);
        hwbhRangeSql += `AND H.HWLX = '${hwlx}' AND H.HWBH LIKE '__${searchShop.recordset[0].CCTJ}%'`
    } else {
        hwlx = searchShop.recordset[0].SPLX;
        hwbhRangeSql += `AND H.HWLX = '${hwlx}' `
    }
    hwbhRangeSql += ` ORDER BY H.HWBH`;
    let hwbhRange = await sql.query(hwbhRangeSql);
    ctx.body = { result: hwbhRange.recordset }
}
// 保存货架位

const keepCarriageRack = async (ctx, next) => {
    const body = ctx.request.body;
    let CKBM = body.CCTJ === 'W' ? (body.BMBM + '01') : body.CCTJ === 'L' ? (body.BMBM + '02') : '';
    let CKMC = body.CCTJ === 'W' ? '常温库' : body.CCTJ === 'L' ? '阴凉库' : '';
    let isExistSql = `SELECT COUNT(YPBM) AS PX FROM YP_HWBH WHERE BMBM = '${body.BMBM}'  AND YPBM = '${body.YPBM}'`;
    let isExistResult = await sql.query(isExistSql);
    let actionSql = '';
    if (isExistResult.recordset[0].PX > 0) {
        actionSql = `UPDATE YP_HWBH SET HWBH = '${body.HWBH}' WHERE BMBM = '${body.BMBM}'  AND YPBM = '${body.YPBM}'`;
    } else {
        actionSql = `INSERT INTO YP_HWBH VALUES('${body.BMBM}','${CKBM}','${CKMC}','${body.YPBM}','${body.HWBH}')`;
    }
    let result = await sql.query(actionSql);
    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }

}

// 获取所有的部门信息

const getBbbm = async (ctx) => {
    let result = await sql.query(`SELECT BM,MC FROM BM_BM`);
    ctx.body = { result: result.recordset }
}

// 删除货架位

const deleteCarriageRack = async (ctx, next) => {
    const body = ctx.request.body;
    let deleteSql = ``;
    body.forEach(item => {
        deleteSql += `DELETE FROM YP_HWBH WHERE BMBM = '${item.BMBM}' AND CKBM = '${item.CKBM}' AND YPBM = '${item.YPBM}' `;
    });
    let lastSql = ` SET XACT_ABORT ON Begin Tran ${deleteSql} Commit Tran `;
    let result = await sql.query(lastSql);

    if (result.rowsAffected[0] > 0) {
        ctx.body = { success: true }
    } else {
        ctx.body = { success: false }
    }

}

// 获取商品的货架位不正确的商品信息

const getHwbhErrorInfo = async (ctx, next) => {
    try {
        let selectSql = `  SELECT K.YPBM,YP.MC,YP.GG,YP.SCCJ,KZL.JXMC,KZL.NFWY,KZL.CCTJ,KZL.SPLX,K.HWBH,YP.JXMC AS SPJXMC  FROM (select YPBM,HWBH from KC_KCDTB where BMBM = '07' AND ISNULL(HWBH,'') != '' AND ISNULL(YPBM,'') != '' AND KCSL > 0 ) k left join  (
        SELECT HW.HWBH,HX.YPBM FROM (
        SELECT case when ZL.SPLX = 'Y' THEN ZL.SPLX+ZL.NFWY+ZL.JX  ELSE SPLX END AS HWLX,ZL.YPBM ,ZL.SPLX,ZL.CCTJ FROM 
        (SELECT case when JXMC = '片剂' THEN 'P' WHEN JXMC = '胶囊' THEN 'J' WHEN JXMC = '丸剂' THEN 'W' WHEN JXMC = '液体制剂' THEN 'Y' ELSE 'G' END AS JX,*  FROM YPZL) ZL
        ) HX  LEFT JOIN HWBH HW  ON HW.HWLX = HX.HWLX AND( HX.SPLX = 'Y' AND SUBSTRING(HW.HWBH,3, 1) = HX.CCTJ OR HX.SPLX != 'Y') where isnull(HW.HWBH,'') != '' AND isnull(HX.YPBM,'') != ''
        ) c 	ON k.HWBH=c.HWBH  AND k.YPBM=c.YPBM  LEFT JOIN BM_YP YP  ON K.YPBM =  YP.BM  LEFT JOIN YPZL KZL ON K.YPBM = KZL.YPBM where  ISNULL(c.YPBM,'') = '' AND ISNULL(c.HWBH,'') = '' GROUP BY K.YPBM,YP.MC,YP.GG,YP.SCCJ,KZL.JXMC,KZL.NFWY,KZL.CCTJ,KZL.SPLX,K.HWBH,YP.JXMC  `;
        let result = await sql.query(selectSql);

        if (result.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '获取成功', data: { result: result.recordset } }
        } else {
            ctx.body = { code: 400, message: '没有搜索到信息' }
        }
    } catch (err) {
        ctx.body = { code: 500, err }
    }
}



module.exports = {
    'POST /getCarriageRackShopInfo': getCarriageRackShopInfo,
    'POST /getCanUseHwbh': getCanUseHwbh,
    'POST /keepCarriageRack': keepCarriageRack,
    'POST /deleteCarriageRack': deleteCarriageRack,
    'POST /getHwbhErrorInfo': getHwbhErrorInfo,
    'GET /getBbbm': getBbbm
}